package com.shangpintechnology.sci;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Vector;

import javax.swing.JOptionPane;

public class DAO {
	private String url;
	// 取得连接的url名，注意sqlserver是dsn名
	private Connection con;
	// 实例化一个Connection对象
	private String driver;

	public DAO() {
		super();
		this.url = "jdbc:oracle:thin:@192.168.0.151:1521:xe";
		this.driver = "oracle.jdbc.driver.OracleDriver";

		try {
			Class.forName(driver);
			this.con = DriverManager.getConnection(url, "zzl", "shangpin");
			Statement stmt = con.createStatement();
			stmt.execute("alter session set nls_date_language='american'");
			stmt.close();

		} catch (java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public boolean insertVipComeInEvent(String vipCode, String name, String level,String rfid) {
		Timestamp t = new Timestamp((new Date()).getTime());
		String strInsertInto = "insert into Z_SCI_SVRHISTORY (VIPCODE,INTIME,VIPLEVEL,RFIDCODE,VIPNAME,BRANCH,SVRID) " +
				"values("+vipCode+",to_timestamp('"+t+"','yyyy-mm-dd hh24:mi:ss.ff'),'"+level+"','"+rfid+"','"+name+"','默认',sci_incomeevent.nextval)";
		System.out.println(strInsertInto);
		Statement stmt;//to_timestamp('2006-01-01 12:10:10.1','yyyy-mm-dd hh24:mi:ss.ff'),
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(strInsertInto);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;

	}

	public boolean updateVipClerkInfo() {
		return false;

	}

	public String[][] getTodayVip() {
		Statement stmt;
		String [][] result = null ;
		try {

			// 数据库连接
			stmt = con.createStatement();
			ResultSet count = stmt.executeQuery("select Count(*) from Z_SCI_SVRHISTORY");
			count.next();
			int iCount = count.getInt(1);
			result = new String[iCount][3];
			count.close();
			Statement stmt2 = con.createStatement();
			ResultSet rs = stmt2.executeQuery("select * from Z_SCI_SVRHISTORY");//today
	 		// 返回一个结果集
			for(int i = 0;i<iCount&&rs.next();i++){
				result[i][0]=(new String(rs.getString("VIPCODE")));

				result[i][1]=(new String(rs.getString("VIPNAME")));

				result[i][2]=(new String(rs.getString("VIPLEVEL")));
				
			}
			rs.close();
			stmt.close();
		} catch (Exception ex) {
			ex.printStackTrace();
	 	}
		return result;
		 

	}

	String[][] getServiceHistory(String vipcode) {
		String[][]clerk = new String[100][2];
		 	String query = "select * from Z_SCI_SVRHISTORY where VIPCODE ='"
				+ vipcode + "'";
		 String [][] result = null ;
		
		Statement stmt;
		 
		try {
			stmt = con.createStatement();
		
			ResultSet count = stmt.executeQuery("select Count(*) from Z_SCI_SVRHISTORY where VIPCODE ='"
				+ vipcode + "'");
			count.next();
			int iCount = count.getInt(1);
			result = new String[iCount][4];
		 	stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
		 	for(int i = 0;i<iCount&&rs.next();i++){
			  result[i][0]=(new String(rs.getString("INTIME")));
			 	String name = null ;
			 	String clerkid = rs.getString("CLERK");
			 	 if(!"0".equals(clerkid)){
					
				try {
					Statement stmt3 = con.createStatement();//"+rs.getString("CLERK")+"
					  	ResultSet rs3 = stmt3.executeQuery("select * from Waiter where id ='"+clerkid+"'");
					 	if(!rs3.next()){
					 		JOptionPane.showMessageDialog(null, vipcode+"=vipcode;未知职员clerk="+clerkid);
					 		return null;
					 	}
					    name=rs3.getString("WAITERNAME");  
						rs3.close();
						stmt3.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				}else{
					name="未知";
				}
				
				result[i][1]=(new String(name));
				result[i][2]=(new String(rs.getString("BRANCH")));
				result[i][3]=(new String(rs.getString("SVRID")));
			  }
			rs.close();
			stmt.close();
		} catch (SQLException ex) {
		ex.printStackTrace();	
		}
		
		 	return result;
		}
	 
	AccountInfo getVip(String code) {

		String query = "select * from z_sci_accountinfo where VIP_CODE ='"
				+ code + "'";
		System.out.println(query);
		Statement stmt;
		AccountInfo accountinfo = null ;
		try {

			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int i;
			i = 1;
			if (rs.next()) {
				accountinfo = new AccountInfo();
				accountinfo.setVipCode(rs.getString("VIP_CODE"));
				accountinfo.setVipName(rs.getString("VIP_NAME"));
				accountinfo.setVipLevel(rs.getString("VIP_LEVEL"));
				accountinfo.setSex(rs.getString("SEX"));
				accountinfo.setAge(rs.getString("AGE"));
				accountinfo.setRiskStage(rs.getString("RISK_STAGE"));
				accountinfo.setBankCode(rs.getString("BANK_CODE"));
				accountinfo.setPassType(rs.getString("PASS_TYPE"));
				accountinfo.setPassNum(rs.getString("PASS_CODE")); 
			}
			rs.close();
			stmt.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return accountinfo;
	}

	public String[][] searchAccountInfo(String name) {

		String query = "select * from z_sci_accountinfo where VIP_NAME like '%"
				+ name + "%'";
		System.out.println(query);
		Statement stmt;
		Vector v = new Vector(100);

		try {

			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			int i;
			i = 1;
			while (rs.next()) {
				String[] strArray = { rs.getString("VIP_CODE"),
						rs.getString("VIP_NAME"), 
						rs.getString("VIP_LEVEL"),
						rs.getString("SEX"),
						rs.getString("AGE"),
						rs.getString("RISK_STAGE"), 
						rs.getString("BANK_CODE"),
						rs.getString("PASS_TYPE"), 
						rs.getString("PASS_CODE"), };
				v.add(strArray);
			}

			rs.close();
			stmt.close();
			} catch (Exception e) {
			e.printStackTrace();
		}
		if (v.isEmpty()) {
			return null;

		} else {
			String[][] result = new String[v.size()][9];

			for (int i = 0; i < result.length; i++) {
				result[i] = (String[]) v.get(i);
			}
			return result;
		}
	}

	public Vector getVipCodeHistory() {
		String query = "select * from Z_SCI_SVRHISTORY";
		Statement stmt;
		Vector v = new Vector(100);

		try {
	stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			Vector v1;
			while (rs.next()) {
				v1 = new Vector(5);
				v1.add(rs.getString("VIPCODE"));
				v1.add(rs.getString("VIPNAME"));
				v1.add(rs.getString("VIPLEVEL"));
				v.add(v1);
			}

			rs.close();
			stmt.close();
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		return v;
	}

	public boolean updateAccountInfo(String vipcode, String username,
			String age, String bankcard, String sex, String riskstage,
			String passtype, String passcode) {
		boolean result = false;
	Statement stmt;
		try {
		stmt = con.createStatement();
			String queryx = "insert into  Z_SCI_ACCOUNTINFO (VIP_CODE,VIP_NAME,AGE,BANK_CODE,SEX,RISK_STAGE,PASS_TYPE,PASS_CODE)"
					+ "values("
					+ "ACCOUNTINFO_ID_SEQ.NEXTVAL"
					+ ",'"
					+ username
					+ "','"
					+ age
					+ "','"
					+ bankcard
					+ "','"
					+ sex
					+ "','"
					+ riskstage
					+ "','"
					+ passtype
					+ "','"
					+ passcode
					+ "')";
			System.out.println(queryx);
			stmt.execute(queryx);
			result = true;
			stmt.close();
	} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public String[] getAccountInfo(String name) {
		if (name == null)
			name = "";
		String query = "select * from Z_SCI_ACCOUNTINFO where VIP_NAME like '%"
				+ name + "%'";
		System.out.println(query);
		Statement stmt;
		ResultSet rs;
		String[] result = new String[9];
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			if (!rs.next())
				return result;
			result[0] = rs.getString("VIP_CODE");
			result[1] = rs.getString("VIP_NAME");
			result[2] = rs.getString("VIP_LEVEL");
			result[3] = rs.getString("SEX");
			result[4] = rs.getString("AGE");
			result[5] = rs.getString("RISK_STAGE");
			result[6] = rs.getString("BANK_CODE");
			result[7] = rs.getString("PASS_TYPE");
			result[8] = rs.getString("PASS_CODE");
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public String getRfidCode(String id) {
		String query = "select RFID_CODE from Z_SCI_RFID_VIPCODE where VIP_CODE = '"
				+ id + "'";
		String rfid = null;
		ResultSet rs = null;
		try {
			Statement stmt =con.createStatement();
		  rs = stmt.executeQuery(query);
			if (rs.next())
			{
				  rfid= rs.getString("RFID_CODE");
				  
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rfid;
	}

	public String getVipCodebyRfid(String epc) {
		String query = "select * from Z_SCI_RFID_VIPCODE where RFID_CODE = '"
				+ epc + "'";
		System.out.println(query);
		String vipcode=null;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if (!rs.next()) {
				return null;
			}
			vipcode= rs.getString("VIP_CODE");

			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vipcode;
	}

	public Vector getHistoryPersonalInfo(String vipCode) {
		String query = "select * from Z_SCI_ACCOUNTINFO where VIP_CODE = '"
				+ vipCode + "'";
		Vector v = new Vector(3);
		try {
			Statement stmt =  con.createStatement();
			ResultSet rs =stmt.executeQuery(query);
			if (!rs.next()) {
				return null;
			}
			v.add(rs.getString("VIP_CODE"));
			v.add(rs.getString("VIP_NAME"));
			v.add(rs.getString("VIP_LEVEL"));
			rs.close();
			stmt.close();
			return v;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void updateStuff(String id, String name) {
		String query = "update waiter set waitername='"+name+"' where id='"+id+"'";
		 try {
				Statement stmt = con.createStatement();
				stmt.execute(query);
				stmt.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}

	public void insertStuff(String text) {//insert into S_Depart(departId,Departname,Departorder)values(S_S_Depart.Nextval,'12345',1);
		String query ="insert into waiter (id,waitername)values(waiter_id_seq.nextval,'"+text+"')";
		System.out.println(query); 
		try {
			Statement stmt = con.createStatement();
			stmt.execute(query);
			stmt.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public boolean removeStuff(String id) {
		String query = "delete from waiter where id="+id;
		System.out.println(query);
		 try {
			Statement stmt = con.createStatement();
			 stmt.execute(query); 
			String cmt = " commit";
			stmt.execute(cmt);
			stmt.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public String[][] getStuffArray2() {
		String[][] str = new String[10][2 ];
		String query = "select * from waiter";
		try {
			Statement stmt = con.createStatement();
			ResultSet rs  = stmt.executeQuery(query);
			
			
			int i =0;
			
			while(rs.next()&&i<10){
				str[i][0]= rs.getString("id");
				str[i][1]= rs.getString("waitername");
				i++;
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return str;
	}

	public void updateAccountName(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set VIP_NAME='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	

	public void updateAccountSex(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set SEX='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateAccountPassType(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set PASS_TYPE='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateAccountPassCode(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set PASS_CODE='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateAccountRisk(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set RISK_STAGE='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateAccountAge(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set AGE='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateAccountLevel(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set VIP_LEVEL='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateAccountBankCode(String id, String value) {
		String query = "update Z_SCI_ACCOUNTINFO set BANK_CODE='"+value+"' where VIP_CODE="+id;
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void removeCustomer(String id) {
		String remove = "delete from  Z_SCI_ACCOUNTINFO where VIP_CODE='"+id+"'";
		try {
			Statement stmt = con.createStatement();
			stmt.execute(remove);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public String getHistoryTime (String vipCode) {
		String query = "select InTime from Z_SCI_SVRHISTORY where VIPCODE = '"
			+ vipCode + "'";
		String inTime = "";
	 try {
		Statement stmt =  con.createStatement();
		ResultSet rs =stmt.executeQuery(query);
		if (!rs.next()) {
			return null;
		}
		 inTime = rs.getString("INTIME");
		rs.close();
		stmt.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		
	}
	return inTime;
	}

	public void updateServiceHistory(String waiterCode,String vipCode) {
		String SVRID = null;
		try {
				Statement stmt =  con.createStatement();
				ResultSet rs = stmt.executeQuery("select Max(SVRID) from Z_SCI_SVRHISTORY where VIPCODE ='"+vipCode+"'");
				rs.next();
				SVRID=rs.getString(1);
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		
		 
		try {
			Statement stmt2 =  con.createStatement();
			stmt2.executeUpdate("update Z_SCI_SVRHISTORY set CLERK ='"+waiterCode+"' where SVRID='" +
					SVRID+"'");
			 
			stmt2.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
	}

}
